# -*- coding: utf-8 -*-
"""
封装数据库操作基础类
"""

import logging
import mysql.connector
from logmod import LogMgr


logger = LogMgr.getLogger('db.StDbMgr')

class Database:

    def __init__(self, *args,**kwargs):
        self.conn = self.connect(*args, **kwargs)

    # 数据库连接
    def connect(self, *args, **kwargs):
        conn = None
        try:
            conn = mysql.connector.connect(**kwargs)
        except mysql.connector.Error as e:
            logger.error('connect fails!{}'.format(e))
            conn = None
        return conn


    def insert(self, sql, params):
        # 数据库插入
        cursor = self.conn.cursor()
        try:
            # Execute the SQL command
            cursor.execute(sql, params)
            # Commit your changes in the database
            self.conn.commit()
            logger.info('insert ok: %s', params)
        except mysql.connector.Error as e:
            logger.error('insert error!{}'.format(e))
            # Rollback in case there is any error
            self.conn.rollback()
        finally:
            cursor.close()

    def insert_many(self, sql, params):
        # 数据库插入
        cursor = self.conn.cursor()
        try:
            # Execute the SQL command
            cursor.executemany(sql, params)
            # Commit your changes in the database
            self.conn.commit()
            logger.info('insert_many ok: %s', params)
        except mysql.connector.Error as e:
            logger.error('insert_many error!{}'.format(e))
            # Rollback in case there is any error
            self.conn.rollback()
        finally:
            cursor.close()

    def update(self, sql, params):

        cursor = self.conn.cursor()
        try:
            # Execute the SQL command
            cursor.execute(sql, params)
            # Commit your changes in the database
            self.conn.commit()
            logger.info('update ok: %s', params)

        except mysql.connector.Error as e:
            logger.error('update error!{}'.format(e))
            # Rollback in case there is any error
            self.conn.rollback()
        finally:
            cursor.close()


    def update_many(self, sql, valueList):
        """
        update many values
        :param sql:
        :param valueList: list of Tuple
        :return:
        """
        cursor = self.conn.cursor()
        try:

            for values in valueList :
                params = values
                 # Execute the SQL command
                cursor.execute(sql, params)

            # Commit your changes in the database
            self.conn.commit()
            logger.info('update_many ok: %s', params)

        except mysql.connector.Error as e:
            logger.error('update_many error!{}'.format(e))
            # Rollback in case there is any error
            self.conn.rollback()
        finally:
            cursor.close()


    # 获取查询结果
    def fetch_one(self, sql, params):
        cursor = self.conn.cursor()
        res = None
        try:
            cursor.execute(sql, params)
            res = cursor.fetchone()

        except mysql.connector.Error as e:
            logger.info("sql = %s\nparams = %s", sql, params)
            logger.error('query error!{}'.format(e))
            res = None
        finally:
            cursor.close()

        return res


    # 获取查询结果集
    def fetch_all(self, sql, params) :
        cursor = self.conn.cursor()
        res = None
        try:
            cursor.execute(sql, params)
            res = cursor.fetchall()

        except mysql.connector.Error as e:
            logger.info("sql = %s\nparams = %s", sql, params)
            logger.error('query error!{}'.format(e))
            res = None
        finally:
            cursor.close()

        return res


    def close(self):
        if (self.conn != None) :
            self.conn.close()



if __name__ == '__main__':

    import mysql.dconnector
    config = {'host': '127.0.0.1',  # 默认127.0.0.1
              'user': 'root',
              'password': '123456',
              'port': 3306,  # 默认即为3306
              'database': 'test',
              'charset': 'utf8'  # 默认即为utf8
              }
    db = Database(**config)
    sqlQuery = 'select * from users'
    result = db.fetch_all(sqlQuery, None)
    logger.info(result)
    db.close()
